package com.dhcc.DRGsHosp.application.impl;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;

import javax.inject.Named;

import org.dayatang.domain.EntityRepository;
import org.dayatang.domain.InstanceFactory;
import org.dayatang.querychannel.JdbcTemplateObj;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.transaction.annotation.Transactional;

import com.dhcc.DRGsHosp.application.DrgsIndexCalApplication;
import com.dhcc.DRGsHosp.application.ForeCastApplication;
import com.dhcc.DRGsHosp.application.SystemConfigApplication;
import com.dhcc.DRGsHosp.core.domain.IndexCal;
import com.dhcc.DRGsHosp.infra.util.ConstantEnum;
import com.dhcc.DRGsHosp.infra.util.DrgsIndex;
import com.dhcc.DRGsHosp.infra.util.GlobalData;
import com.dhcc.DRGsHosp.infra.util.MonitorResultUtil;
import com.dhcc.DRGsHosp.infra.util.StringUtils;

/**
 * @title		:ForeCastApplicationImpl.java
 * @description	:
 * @author		:lileizheng
 * @date		:2015年6月8日
 * @version     :1.0
 * @copyright   :DHCC 2013-2014 
 */
@Named
@Transactional
public class ForeCastApplicationImpl implements ForeCastApplication {

	@Autowired
	SystemConfigApplication systemConfigApplication;
	@Autowired  
	   private JdbcTemplateObj jdbcTemplateObj;
    private EntityRepository repository;

	private EntityRepository getRepository() {
		if (repository == null) {
			repository = InstanceFactory.getInstance(
					EntityRepository.class, "repository");
		}
		return repository;
	}

	@Override
	public List<Object[]> getMDCList(IndexCal IndexCal) {
		String dateFrom = IndexCal.getDateFrom();
		dateFrom = MonitorResultUtil.getStartDateMonth(dateFrom);
		String dateTo = IndexCal.getDateTo();
		dateTo = MonitorResultUtil.getEndDateMonth(dateTo);
		StringBuilder sql = new StringBuilder();
		sql.append(" select count(a.id), e.mdc_code, e.mdc_desc from DRGs.t_medicalrecord a \n ");
		sql.append(" left join DRGs.t_patientdrg b \n ");
		sql.append(" on a.id=b.parid \n ");
		sql.append(" left join DRGs.t_drgs c \n ");
		sql.append(" on b.pardrg_code=c.drg_code and b.ver_code=c.ver_code \n ");
		sql.append(" left join DRGs.t_adrg d \n ");
		sql.append(" on d.adrg_code=c.adrg_code and d.ver_code=c.ver_code \n ");
		sql.append(" left join DRGs.t_mdcgroup e \n ");
		sql.append(" on e.mdc_code=d.mdc_code and e.ver_code=d.ver_code \n ");
		
		if("MYSQL".equals(ConstantEnum.DBTYPE)){
			sql.append(" where a.out_date between str_to_date('"+dateFrom+"','%Y-%m-%d') and str_to_date('"+dateTo+"', '%Y-%m-%d') \n ");
		}else{
			sql.append(" where a.out_date between to_date('"+dateFrom+"','yyyy-MM-dd') and to_date('"+dateTo+"', 'yyyy-MM-dd') \n ");
		}
		
		sql.append(" and a.in_days<=60 \n ");
		sql.append(" and b.pardrg_code is not null \n ");
		sql.append(" group by e.mdc_code, e.mdc_desc \n ");
		List<Object[]> resultList = getRepository().createSqlQuery(sql.toString()).list();
		return resultList;
	}

	@Override
	public List<Object[]> getAdrgList(IndexCal IndexCal) {
		String dateFrom = IndexCal.getDateFrom();
		dateFrom = MonitorResultUtil.getStartDateMonth(dateFrom);
		String dateTo = IndexCal.getDateTo();
		dateTo = MonitorResultUtil.getEndDateMonth(dateTo);
		StringBuilder sql = new StringBuilder();
		sql.append(" select count(a.id), d.adrg_code, d.adrg_desc from DRGs.t_medicalrecord a \n ");
		sql.append(" left join DRGs.t_patientdrg b \n ");
		sql.append(" on a.id=b.parid \n ");
		sql.append(" left join DRGs.t_drgs c \n ");
		sql.append(" on b.pardrg_code=c.drg_code and b.ver_code=c.ver_code \n ");
		sql.append(" left join DRGs.t_adrg d \n ");
		sql.append(" on d.adrg_code=c.adrg_code and d.ver_code=c.ver_code \n ");
		sql.append(" left join DRGs.t_mdcgroup e \n ");
		sql.append(" on e.mdc_code=d.mdc_code and e.ver_code=d.ver_code \n ");
		
		if("MYSQL".equals(ConstantEnum.DBTYPE)){
			sql.append(" where a.out_date between str_to_date('"+dateFrom+"','%Y-%m-%d') and str_to_date('"+dateTo+"', '%Y-%m-%d') \n ");
		}else{
			sql.append(" where a.out_date between to_date('"+dateFrom+"','yyyy-MM-dd') and to_date('"+dateTo+"', 'yyyy-MM-dd') \n ");
		}
		
		sql.append(" and a.in_days<=60 \n ");
		sql.append(" and b.pardrg_code is not null \n ");
		sql.append(" and e.mdc_code='"+IndexCal.getParmdcCode()+"' \n ");
		sql.append(" group by  d.adrg_code, d.adrg_desc \n ");
		List<Object[]> resultList = getRepository().createSqlQuery(sql.toString()).list();
		return resultList;
	}

	@Override
	public List<Object[]> getDrgList(IndexCal IndexCal) {
		String dateFrom = IndexCal.getDateFrom();
		dateFrom = MonitorResultUtil.getStartDateMonth(dateFrom);
		String dateTo = IndexCal.getDateTo();
		dateTo = MonitorResultUtil.getEndDateMonth(dateTo);
		StringBuilder sql = new StringBuilder();
		sql.append(" select count(a.id), c.drg_code, c.drg_desc from DRGs.t_medicalrecord a \n ");
		sql.append(" left join DRGs.t_patientdrg b \n ");
		sql.append(" on a.id=b.parid \n ");
		sql.append(" left join DRGs.t_drgs c \n ");
		sql.append(" on b.pardrg_code=c.drg_code and b.ver_code=c.ver_code \n ");
		sql.append(" left join DRGs.t_adrg d \n ");
		sql.append(" on d.adrg_code=c.adrg_code and d.ver_code=c.ver_code \n ");
		sql.append(" left join DRGs.t_mdcgroup e \n ");
		sql.append(" on e.mdc_code=d.mdc_code and e.ver_code=d.ver_code \n ");
		
		if("MYSQL".equals(ConstantEnum.DBTYPE)){
			sql.append(" where a.out_date between str_to_date('"+dateFrom+"','%Y-%m-%d') and str_to_date('"+dateTo+"', '%Y-%m-%d') \n ");
		}else{
			sql.append(" where a.out_date between to_date('"+dateFrom+"','yyyy-MM-dd') and to_date('"+dateTo+"', 'yyyy-MM-dd') \n ");
		}
		
		sql.append(" and a.in_days<=60 \n ");
		sql.append(" and b.pardrg_code is not null \n ");
		sql.append(" and d.adrg_code='"+IndexCal.getParadrgCode()+"' \n ");
		sql.append(" group by c.drg_code, c.drg_desc \n ");
		List<Object[]> resultList = getRepository().createSqlQuery(sql.toString()).list();
		return resultList;
	}

	//获取医保基金类型描述
	private String getMedInsDesc() {
		String medInsType = "''";
		String insType = GlobalData.sysConfig.get("MED_INS_TYPE");
		String[] insTypeArr = insType.split("θ");
		int l = insTypeArr.length;
		for (int i = 0; i < l; i++) {
			String ins = insTypeArr[i];
			String tmpInsType = "";
			tmpInsType = ins.split("#")[1];
			tmpInsType = "'" + tmpInsType + "'";
			tmpInsType = tmpInsType.replaceAll("\\+", "','");
			if (i == 0) {
				medInsType = tmpInsType;
			} else {
				medInsType += "," + tmpInsType;
			}
		}
		return medInsType;
	}
	@Override
	public Map<String, Object> getHospData(IndexCal m) {
		//获取医保总基金实际值涉及的医保类型
		String hiType = getMedInsDesc();
		
		String dateFrom = m.getDateFrom();
		dateFrom = MonitorResultUtil.getStartDateMonth(dateFrom);
		String dateTo = m.getDateTo();
		dateTo = MonitorResultUtil.getEndDateMonth(dateTo);
		
		StringBuilder sql = new StringBuilder();
		sql.append(" select avg(in_days), case when count(1)<>0 then sum(costamount)/count(1) else 0 end,count(1),sum(drugcost), \n ");
		sql.append(" sum(case when c.parid is not null and c.hi_type in ('基本医疗保险') then c.fund_pay else 0 end) as HCTotalFund, \n ");
		sql.append(" case when sum(case when c.parid is not null then 1 else 0 end )<>0 then sum(case when c.parid is not null then t.costamount else 0 end)/sum(case when c.parid is not null then 1 else 0 end ) else 0 end as HCAvgFee, \n ");
		sql.append(" sum(case when c.parid is not null then 1 else 0 end ) as HCCaseCnt , \n ");
		sql.append(" sum(case when c.parid is not null then t.drugcost else 0 end) as HCDrugFee \n ");
		sql.append(" from DRGs.T_MEDICALRECORD t \n ");
		sql.append(" left join DRGs.t_patientdrg d \n ");
		sql.append(" on t.id=d.parid \n ");
		sql.append(" left join DRGs.t_medins c \n ");
		sql.append(" on t.id=c.parid \n ");
		sql.append(" left join DRGs.t_Department dep \n ");
		sql.append(" on dep.dep_code=t.out_dep_code \n ");
		
		if("MYSQL".equals(ConstantEnum.DBTYPE)){
			sql.append(" where t.out_date between str_to_date('"+dateFrom+"','%Y-%m-%d') and str_to_date('"+dateTo+"', '%Y-%m-%d') \n ");
		}else{
			sql.append(" where t.out_date between to_date('"+dateFrom+"','yyyy-MM-dd') and to_date('"+dateTo+"', 'yyyy-MM-dd') \n ");
		}
		
		
		if(StringUtils.hasLength(m.getPardepgroupCode()) && !"DRG_DHCC".equals(m.getPardepgroupCode())){
			sql.append(" and dep.pardepgroup_code = '"+m.getPardepgroupCode()+"' ");
		}
		if(StringUtils.hasLength(m.getPardepCode()) && !"DRG_DHCC".equals(m.getPardepCode())){
			sql.append(" and dep.dep_code = '"+m.getPardepCode()+"' ");
		}
		Object[] obj = getRepository().createSqlQuery(sql.toString()).singleResult();
		Map<String, Object> resultm = new HashMap<String, Object>();
		String[] columns = {"AvgDays", "AvgFee", "TotalCaseCnt", "DrugFee", "HCTotalFund", "HCAvgFee", "HCCaseCnt", "HCDrugFee"};
		for(int i=0; i<columns.length; i++){
			String column = columns[i];
			resultm.put(column, obj[i]);
		}
		
		sql = new StringBuilder();
		sql.append(" select sum(avgfee*totalcasecnt/as_avg_fee)/sum(totalcasecnt) feeEff,  \n ");
		sql.append(" sum(avgdays*totalcasecnt/as_avg_days)/sum(totalcasecnt) timeEff from( \n ");
		sql.append(" select w.*, t.as_avg_fee, t.as_avg_Days from( \n ");
		sql.append(" select b.pardrg_code, avg(costamount) avgfee, avg(in_days) avgdays, count(1) totalcasecnt from DRGs.t_medicalrecord a \n ");
		sql.append(" left join DRGs.t_patientdrg b \n ");
		sql.append(" on a.id=b.parid \n ");
		sql.append(" left join DRGs.t_department dep \n ");
		sql.append(" on dep.dep_code=a.out_dep_code \n ");
		
		if("MYSQL".equals(ConstantEnum.DBTYPE)){
			sql.append(" where a.out_date between str_to_date('"+dateFrom+"','%Y-%m-%d') and str_to_date('"+dateTo+"', '%Y-%m-%d') \n ");
		}else{
			sql.append(" where a.out_date between to_date('"+dateFrom+"','yyyy-MM-dd') and to_date('"+dateTo+"', 'yyyy-MM-dd') \n ");
		}
		
		if(StringUtils.hasLength(m.getPardepgroupCode())){
			sql.append(" and dep.pardepgroup_code = '"+m.getPardepgroupCode()+"' ");
		}
		sql.append(" group by b.pardrg_code \n ");
		sql.append(" ) w left join ( \n ");
		sql.append(" select * from DRGs.t_Annualstd where as_year=( \n ");
		sql.append(" select max(as_year) from DRGs.t_Annualstd \n ");
		sql.append(" )) t \n ");
		sql.append(" on w.pardrg_code=t.as_drg_code \n ");
		sql.append(" ) \n ");
		Object[] obj1 = getRepository().createSqlQuery(sql.toString()).singleResult();
		resultm.put("FeeEff", obj1[0]);
		resultm.put("TimeEff", obj1[1]);
		return resultm;
	}
/**
 * 
 *  @Description：获取科室新增床日
 *  @Author LiuHongJun  
 *  @see com.dhcc.DRGsHosp.application.ForeCastApplication#getAddBedDaysByDep(java.lang.String, java.lang.String)
 */
	@Override
	public double getAddBedDaysByDep(String ccode, String year) {
		double addDays = 0;
		String sql = " select beds*bed_days beddays from DRGs.T_DEPBEDS  where dep_code='"+ccode+"' ";
		sql += " and add_year='"+year+"'";
		List<Object> resultList = getRepository().createSqlQuery(sql).list();
		if(resultList.size()>0){
			Object obj = resultList.get(0);
			addDays = obj!=null?Double.parseDouble(obj.toString()):0;
		}
		return addDays;
	}
	
	@Override
	public double getHcRatioByDep(String ccode, String year) {
		double value = 0;
		String sql = " select hcratio from DRGs.T_DEPBEDS  where dep_code='"+ccode+"' ";
		sql += " and add_year='"+year+"'";
		List<Object> resultList = getRepository().createSqlQuery(sql).list();
		if(resultList.size()>0){
			Object obj = resultList.get(0);
			value = obj!=null?Double.parseDouble(obj.toString()):0;
		}
		return value;
	}
	
	@Override
	public double getHcFundRatioByDep(String ccode, String year) {
		double value = 0;
		String sql = " select hcfundratio from DRGs.T_DEPBEDS  where dep_code='"+ccode+"' ";
		sql += " and add_year='"+year+"'";
		List<Object> resultList = getRepository().createSqlQuery(sql).list();
		if(resultList.size()>0){
			Object obj = resultList.get(0);
			value = obj!=null?Double.parseDouble(obj.toString()):0;
		}
		return value;
	}
/**
 * 
 *  @Description：获取预测时使用的年度标杆
 *  @Author LiuHongJun  
 *  @see com.dhcc.DRGsHosp.application.ForeCastApplication#getDRGsNanualData()
 */
	@Override
	public Map<String, Object> getDRGsNanualData(String stdYear) {
		Map<String, Object> resultMap = new HashMap<>();
		StringBuilder sql = new StringBuilder();
		sql.append(" select as_drg_code, as_avg_fee, as_avg_days from DRGs.t_Annualstd  \n ");
		sql.append(" where as_year='"+stdYear+"' \n ");
		List<Object[]> resultList = getRepository().createSqlQuery(sql.toString()).list();
		for(Object[] objs: resultList){
			Map<String, Object> m = new HashMap<>();
			m.put("AvgFee", objs[1]);
			m.put("AvgDays", objs[2]);
			resultMap.put(objs[0].toString(), m);
		}
		return resultMap;
	}

	@Override
	public List<Object[]> getNewDepartment(String year, String depGroupCode) {
		StringBuilder sql =  new StringBuilder();
		sql.append(" select a.dep_code, b.dep_name from drgs.t_depbeds a \n ");
		sql.append(" left join drgs.t_department b \n ");
		sql.append(" on a.dep_code=b.dep_code \n ");
		sql.append(" where a.is_new='1' \n ");
		if(StringUtils.hasLength(depGroupCode)){
			sql.append(" and b.pardepgroup_code='"+depGroupCode+"' \n ");
		}
		List<Object[]> resultList = getRepository().createSqlQuery(sql.toString()).list();
		return resultList;
	}

	@Override
	public List<Object[]> getNewDepartmentDRGsData(String depCode, String year) {
		String sql = "select drg_code, totalcasecnt, avgdays, avgfee, avgdrugfee from drgs.t_depnewplandrgs where dep_code='"+depCode+"' and pyear='"+year+"'";
		List<Object[]> list = getRepository().createSqlQuery(sql).list();
		return list;
	}

	@Override
	public List<Object[]> getNewPlan(String depCode, String addYear) {
		StringBuilder sql = new StringBuilder();
		sql.append(" select a.dep_code, b.dep_name, beds, add_year, bed_days, hcratio, hcfundratio, is_new  \n ");
		sql.append(" from drgs.T_DEPBEDS a \n ");
		sql.append(" left join drgs.t_department b \n ");
		sql.append(" on a.dep_code=b.dep_code \n ");
		sql.append(" where 1=1 \n ");
		if(StringUtils.hasLength(depCode)){
			sql.append(" and a.dep_code='"+depCode+"' \n ");
		}
		if(StringUtils.hasLength(addYear)){
			sql.append(" and a.add_year='"+addYear+"' \n ");
		}
		List<Object[]> list = getRepository().createSqlQuery(sql.toString()).list();
		return list;
	}

	@Override
	public void savePlan(Map<String, Object> json) {
		String depCode = json.get("depCode")!=null?json.get("depCode").toString():"";
		String addYear = json.get("addYear")!=null?json.get("addYear").toString():"";
		String beds = json.get("beds")!=null?json.get("beds").toString():"0";
		String bedDays = json.get("bedDays")!=null?json.get("bedDays").toString():"0";
		String hcratio = json.get("hcratio")!=null?json.get("hcratio").toString():"0";
		String hcfundratio = json.get("hcfundratio")!=null?json.get("hcfundratio").toString():"0";
		String isNew = json.get("isNew")!=null?json.get("isNew").toString():"0";
		String sqlStr = "select 1 from drgs.t_depbeds t where t.dep_code='"+depCode+"' and add_year='"+addYear+"' ";
		List<Object[]> list = getRepository().createSqlQuery(sqlStr).list();
		if(list.size()>0){
			sqlStr = "update drgs.t_depbeds set beds="+beds+", bed_days="+bedDays+", hcratio="+hcratio+", hcfundratio="+hcfundratio+", is_new='"+isNew+"' where dep_code='"+depCode+"' and add_year='"+addYear+"'";
			getRepository().createSqlQuery(sqlStr).executeUpdate();
		}else{
			sqlStr = "insert into drgs.t_depbeds(dep_code, add_year, beds, bed_days, hcratio, hcfundratio, is_new) values ('"+depCode+"', '"+addYear+"', "+beds+", "+bedDays+", "+hcratio+", "+hcfundratio+", '"+isNew+"')";
			getRepository().createSqlQuery(sqlStr).executeUpdate();
		}
	}

	@Override
	public void delPlan(String depCode, String addYear) {
		String sql = "select 1 from drgs.T_DEPNEWPLANDRGS t where dep_code='"+depCode+"' and pyear='"+addYear+"'";
		List<Object[]> list = getRepository().createSqlQuery(sql).list();
		sql = " begin \n ";
		if(list.size()>0){
			sql += "delete from drgs.T_DEPNEWPLANDRGS t where dep_code='"+depCode+"' and pyear='"+addYear+"';";
		}
		sql += "delete from drgs.t_depbeds t where t.dep_code='"+depCode+"' and add_year='"+addYear+"'; \n ";
		sql += "end; \n ";
		getRepository().createSqlQuery(sql).executeUpdate();
	}

	@Override
	public List<Object[]> getNewPlanDRGs(String depCode, String addYear) {
		StringBuilder sql = new StringBuilder();
		sql.append(" select drg_code, totalcasecnt, avgdays, avgfee, avgdrugfee  \n ");
		sql.append(" from drgs.T_DEPNEWPLANDRGS a \n ");
		sql.append(" where 1=1 \n ");
		if(StringUtils.hasLength(depCode)){
			sql.append(" and a.dep_code='"+depCode+"' \n ");
		}
		if(StringUtils.hasLength(addYear)){
			sql.append(" and a.pyear='"+addYear+"' \n ");
		}
		List<Object[]> list = getRepository().createSqlQuery(sql.toString()).list();
		return list;
	}

	@Override
	public void saveNewPlanDRGs(List<Map<String, Object>> json, String depCode, String addYear) {
		String st = "select count(1) from drgs.T_DEPNEWPLANDRGS where dep_code='"+depCode+"' and pyear='"+addYear+"'";
		Object transCntObj = getRepository().createSqlQuery(st).singleResult();
		int transCnt_o = Integer.parseInt(transCntObj.toString());
		int transCnt_n = json.size();
		StringBuilder sql = new StringBuilder("declare \n ");
		sql.append("  ecnt integer; \n ");
		sql.append("begin \n ");
		for(int index=0; index<json.size(); index++){
			Map<String, Object> j = json.get(index);
			sql.append("  select count(1) into ecnt from drgs.T_DEPNEWPLANDRGS where dep_code='"+depCode+"' and pyear='"+addYear+"' and P_INDEX="+index+"; \n ");
			sql.append("  if ecnt>0 then \n ");
			sql.append("    update drgs.T_DEPNEWPLANDRGS set drg_Code='"+j.get("drgCode")+"', totalcasecnt='"+j.get("totalCaseCnt")+"', avgdays='"+j.get("avgDays")+"', avgfee='"+j.get("avgFee")+"', avgdrugfee="+j.get("avgDrugFee")+" where dep_code='"+depCode+"' and pyear='"+addYear+"' and p_index="+index+"; \n ");
			sql.append("  else \n ");
			sql.append("    insert into drgs.T_DEPNEWPLANDRGS(dep_code, drg_code, pyear, totalcasecnt, avgdays, avgfee, avgdrugfee, p_index) values('"+depCode+"', '"+j.get("drgCode")+"', '"+addYear+"', '"+j.get("totalCaseCnt")+"', '"+j.get("avgDays")+"', '"+j.get("avgFee")+"', '"+j.get("avgDrugFee")+"', "+index+"); \n ");
			sql.append("  end if; \n ");
		}
		if(transCnt_o > transCnt_n){
			//清除多余数据
			sql.append("  select count(1) into ecnt from drgs.T_DEPNEWPLANDRGS where dep_code='"+depCode+"' and pyear='"+addYear+"'; \n ");
			sql.append("  if ecnt>"+transCnt_n+" then \n ");
			sql.append("    delete from drgs.T_DEPNEWPLANDRGS where dep_code='"+depCode+"' and pyear='"+addYear+"' and p_index>="+transCnt_n+"; \n ");
			sql.append("  end if; \n ");
		}
		sql.append("end; \n ");
		getRepository().createSqlQuery(sql.toString()).executeUpdate();
	}
	/**
	 * 
	 *  @Description：保存为绩效标杆
	 *  @Author LiuHongJun  
	 *  @see com.dhcc.DRGsHosp.application.ForeCastApplication#saveToQuotamanage(java.lang.String, java.lang.String, java.lang.String)
	 */
	@Override
	public void saveToQuotamanage(String stdType,String saveType,String planYear,String roleName) {
		String sql=" SELECT SUBSTR(QE.ID,0,INSTR(QE.ID,'_',1)-1) CODE,QE.PARQUOTA_CODE,QE.EXPECTED_VALUE FROM DRGS.T_QUOTAEXPECTED QE \n";
		sql+=" WHERE QE.OBJ_TYPE='"+stdType+"' AND QE.EXPECTED_CODE='"+saveType+"' AND QE.EXPECTED_YEAR='"+planYear+"' AND QE.ROLE_NAME='"+roleName+"' \n";
		String role= StringUtils.stringToAscii("医保");
		role=role.replaceAll(",", "");
		if(!role.equals(roleName)) {
			sql+=" and substr(QE.PARQUOTA_CODE,0,2) <>'HC' ";
		}
		List<Map<String, Object>> resultList = jdbcTemplateObj.queryForList(sql);//getRepository().createSqlQuery(sql.toString()).list();
		if(!resultList.isEmpty()) {
			for(Map<String, Object> map:resultList){
				Connection connection =null;
				try{
					connection=jdbcTemplateObj.getDataSource().getConnection();
					connection.setAutoCommit(true);
					CallableStatement statement = connection.prepareCall("call drgs.proc_updatequota_manager(?,?,?,?,?,?)");
					if("hosp".equals(stdType)){
						statement.setString(1, "hosp");
						statement.setString(2,"");
						
					}else if("depGroup".equals(stdType)){
						statement.setString(1,"depGroup" );
						statement.setString(2,map.get("CODE").toString());
						
					}else{
						statement.setString(1, "dep");
						statement.setString(2,map.get("CODE").toString());
					}
					statement.setString(3, map.get("PARQUOTA_CODE").toString());
					statement.setString(4, planYear);
					statement.setString(6, map.get("EXPECTED_VALUE")==null?null:map.get("EXPECTED_VALUE").toString());
					//区分医保和其他角色
					if(String.valueOf(map.get("PARQUOTA_CODE")).indexOf("HC")!=-1){
						statement.setString(5, "1");	
					}else{
						statement.setString(5, "0");
					}
					statement.execute();
				}catch(Exception e){
					e.printStackTrace();
				}finally{
					try{
						connection.close();	
					}catch(SQLException e){
						e.printStackTrace();
					}
				}	
			}
		}		
	}
/**
 * 
 *  @Description：获取测算的关联指标
 *  @Author liuhongjun 
 *  @see com.dhcc.DRGsHosp.application.ForeCastApplication#findRelevanceQuotaCode(java.lang.String)
 */
	@Override
	public List<Map<String, Object>> findRelevanceQuotaCode(String quota) {
		return jdbcTemplateObj.queryForList("select t.quota_code from T_QUOTASELECTDTL t where t.quota_id='"+quota+"'");
	}
/**
 *  @Description：获取该科室组下科室
 *  @Author：liuhongjun 
 *  @see com.dhcc.DRGsHosp.application.ForeCastApplication#getDepartmentByDepGroupCode(java.lang.String)
 */
@Override
public List<Map<String, Object>> getDepartmentByDepGroupCode(String depGroupCode) {
	return jdbcTemplateObj.queryForList("SELECT d.dep_code,d.dep_name FROM DRGS.t_Department d where d.pardepgroup_code='"+depGroupCode+"'");
}
/**
 *  @Description：获取计算标杆
 *  @Author：liuhongjun 
 *  @see com.dhcc.DRGsHosp.application.ForeCastApplication#getDrgStdYear(java.lang.String)
 */
@Override
public List<Map<String, Object>> getDrgStdYear(String asId) {
	return jdbcTemplateObj.queryForList("SELECT b.as_drg_code DRGCODE ,b.as_avg_days AVGDAYS,b.as_avg_fee AVGFEE FROM DRGS.t_Annualstd b WHERE b.as_id='"+asId+"'");
}

	@Autowired
	public DrgsIndexCalApplication drgsIndexCalApplication;
	
	@Override
	public List<Map<String, Object>> foreCastHospital(String dateFrom, String dateTo, String asId, String forecastYear, String monitorType) {
		String sql = drgsIndexCalApplication.getSqlByQuotaCode("ForeCastHosp");
		if(!StringUtils.hasLength(sql)){
			return new LinkedList<>();
		}
		String roleCode = DrgsIndex.getRoleCode();
		sql = sql.replaceAll(":dateFrom", "'"+dateFrom+"'");
		sql = sql.replaceAll(":dateTo", "'"+dateTo+"'");
		sql = sql.replaceAll(":foreYear", "'"+forecastYear+"'");
		sql = sql.replaceAll(":asId", "'"+asId+"'");
		sql = sql.replaceAll(":roleCode", "'"+roleCode+"'");
		if("depGroup".equals(monitorType)){
			sql = sql.replaceAll(":ccode1", "depgroup_code");
			sql = sql.replaceAll(":ccode2", "pardepgroup_code");
		}else if("dep".equals(monitorType)){
			sql = sql.replaceAll(":ccode1", "dep_code");
			sql = sql.replaceAll(":ccode2", "dep_code");
		}
		List<Map<String, Object>> resultList = jdbcTemplateObj.queryForList(sql.toString());
		return resultList;
	}
	
	@Override
	public List<Map<String, Object>> foreCastDepgroup(String dateFrom, String dateTo, String asId, String forecastYear, String monitorType) {
		String sql = drgsIndexCalApplication.getSqlByQuotaCode("ForeCastDepgroup");
		if(!StringUtils.hasLength(sql)){
			return new LinkedList<>();
		}
		String roleCode = DrgsIndex.getRoleCode();
		sql = sql.replaceAll(":dateFrom", "'"+dateFrom+"'");
		sql = sql.replaceAll(":dateTo", "'"+dateTo+"'");
		sql = sql.replaceAll(":foreYear", "'"+forecastYear+"'");
		sql = sql.replaceAll(":asId", "'"+asId+"'");
		sql = sql.replaceAll(":roleCode", "'"+roleCode+"'");
		if("depGroup".equals(monitorType)){
			sql = sql.replaceAll(":ccode1", "depgroup_code");
			sql = sql.replaceAll(":ccode2", "pardepgroup_code");
		}else if("dep".equals(monitorType)){
			sql = sql.replaceAll(":ccode1", "dep_code");
			sql = sql.replaceAll(":ccode2", "dep_code");
		}
		List<Map<String, Object>> resultList = jdbcTemplateObj.queryForList(sql.toString());
		return resultList;
	}
	
	@Override
	public List<Map<String, Object>> foreCastDep(String dateFrom, String dateTo, String asId, String forecastYear, String monitorType) {
		String sql = drgsIndexCalApplication.getSqlByQuotaCode("ForeCastDep");
		if(!StringUtils.hasLength(sql)){
			return new LinkedList<>();
		}
		String roleCode = DrgsIndex.getRoleCode();
		sql = sql.replaceAll(":dateFrom", "'"+dateFrom+"'");
		sql = sql.replaceAll(":dateTo", "'"+dateTo+"'");
		sql = sql.replaceAll(":foreYear", "'"+forecastYear+"'");
		sql = sql.replaceAll(":asId", "'"+asId+"'");
		sql = sql.replaceAll(":roleCode", "'"+roleCode+"'");
		if("depGroup".equals(monitorType)){
			sql = sql.replaceAll(":ccode1", "depgroup_code");
			sql = sql.replaceAll(":ccode2", "pardepgroup_code");
		}else if("dep".equals(monitorType)){
			sql = sql.replaceAll(":ccode1", "dep_code");
			sql = sql.replaceAll(":ccode2", "dep_code");
		}
		List<Map<String, Object>> resultList = jdbcTemplateObj.queryForList(sql.toString());
		return resultList;
	}
}
